##xls表格批量转换为Txt
# -*- coding: utf-8 -*- 
import os
import sys
import xlrd
import re

ProjectPos = os.path.abspath(os.path.dirname(os.getcwd()))  # 项目位置
file_dir = sys.path[0] + r'\Xlsx'  # xls表格所在位置
file_dirOut = ProjectPos + r'\Assets\Resources\DataTables'  # txt表格输出位置
file_CsdirOut = ProjectPos + r'\Assets\Scripts\GameLogic\DataTables\DrCs'  # cs输出位置
file_CsModelPath = sys.path[0] + r'\default\defaultclass.cs'  # 模版所在位置
file_DataTableAllPath = ProjectPos + r'\Assets\Scripts\Base\DataTables\DataTableAllName.cs'
AllCsBag = []  # q全部数据包


# 打开excel--------------------------------------------------------------------------------------------------------------
def open_excel(filename):
    try:
        data = xlrd.open_workbook(filename)
        return data
    except IOError:
        print("Error: 没有找到文件或读取文件失败")


# 打开当前文件夹的全部文件--------------------------------------------------------------------------------------------------------------
def get_flile_list():
    return os.listdir(file_dir)


# 首字母大写转换函数
def firstCharUpper(s):
    return s[:1].upper() + s[1:]


class CsBag:
    cName = ""  # 表名字
    cNameExplain = ""  # 表名字
    cAttr = []  # 属性
    cExplain = []  # 说明
    cTpey = []  # 类型


class CsArrayBag:  # 数组包
    cNum = 0  # 数量
    cId = []  # 表中ID
    cName = ""  # 表中去除ID的名字
    cType = ""  # 类型
    cExplain = ""  # 注释


# 写入整体配置文件--------------------------------------------------------------------------------------------------------------
def Set_CsName():
    CsPath = file_DataTableAllPath
    if os.path.exists(CsPath):
        os.remove(CsPath)
    Csfile = open(CsPath, 'a+')
    Csfile.write("public class DataTableAllName{\n\tpublic static readonly string[] DataTableNames = new string[]{\n")
    for curbag in AllCsBag:
        Csfile.write("\t\"" + curbag.cName + "\",\n")

    Csfile.write("};}")
    Csfile.close()
    print("CS Name Ok")


# 写入CS文件--------------------------------------------------------------------------------------------------------------
def getSummary(s):
    return "///<summary>{0}</summary>\n".format(s)


def toTyeps(ztype):  # 扩展类型 用于基础类型加密
    if ztype == "int":
        return "int"
    if ztype == "float":
        return "float"
    if ztype == "string":
        return "string"
    return ztype


def CsBagToCs():
    CsModeF = open(file_CsModelPath, 'r', encoding='utf-8')
    CsText = CsModeF.read()
    CsModeF.close()

    for curbag in AllCsBag:
        CsPath = file_CsdirOut + r'\\' + curbag.cName + ".cs"
        if os.path.exists(CsPath):
            os.remove(CsPath)
        Csfile = open(CsPath, 'a+', encoding='utf-8')
        cs = "" + CsText
        # ----------- -----------
        keyText = ""
        classText = ""
        classInitText = ""
        dicAttr = {"": CsArrayBag()}
        for i in range(len(curbag.cAttr)):
            summary = getSummary(curbag.cExplain[i])
            ctype = toTyeps(curbag.cTpey[i])
            btype = curbag.cTpey[i]
            cAttr = curbag.cAttr[i]
            
            keyText += '\t{0}\tpublic const string _{1}=\"{1}\";\n'.format(summary, cAttr)
            classText += '\t\t{0}\t\tpublic {1} {2};\n'.format(summary, ctype, cAttr)
            classInitText += "\t\t\t_line.{0} = Instance().get{1}ById(id, _{0});\n".format(cAttr, btype)

            # List--STR-------------- ----------- ----------- -----------
            cNmae = re.sub(u"([^\u0041-\u005a\u0061-\u007a])", "", cAttr)
            cId = re.sub(u"([^\u0030-\u0039])", "", cAttr)

            if cNmae in dicAttr:
                dicAttr[cNmae].cNum += 1
            else:
                csArrtbag = CsArrayBag()
                dicAttr[cNmae] = csArrtbag
                csArrtbag.cNum = 1
                csArrtbag.cId = []
            csarrtbag = dicAttr[cNmae]
            csarrtbag.cName = cNmae
            csarrtbag.cId.append(cId)
            csarrtbag.cType = ctype
            csarrtbag.cExplain = summary
            # List--END-------------- ----------- ----------- -----------

        for key in dicAttr:
            attrBag = dicAttr[key]
            if attrBag.cNum > 2:
                ctypes = attrBag.cType
                cNames = attrBag.cName
                cExplains = attrBag.cExplain

                classInitText += '\n'
                classText += '\t\t{0}\t\tpublic Dictionary<int,{1}> {2}List;\n'.format(cExplains, ctypes, cNames)
                classInitText += '\t\t\t_line.{0}List = new Dictionary<int,{1}>();\n'.format(cNames, ctypes)
                for num in range(0, attrBag.cNum):
                    classInitText += '\t\t\t_line.{0}List.Add({1},_line.{0}{1});\n'.format(cNames, attrBag.cId[num])

        cs = cs.replace('defaultclass', curbag.cName)
        cs = cs.replace('//TheReservedText', keyText)
        cs = cs.replace('//ReservedTextName', getSummary(curbag.cNameExplain))
        cs = cs.replace('//TheReservedValueText', classText)
        cs = cs.replace('//TheReservedInitializeText', classInitText)

        Csfile.write(cs)
        Csfile.close()
        print(curbag.cName + ".cs Ok")


# 写入TXT文件-------------------------------------------------------------------------------------------------------------
def excel2txt(suffix, byindex, split_str):
    filelist = get_flile_list()
    for file in filelist:
        basefile = os.path.splitext(file)
        if basefile[1] == suffix:
            txtName = "DR" + basefile[0]
            target_file = txtName + '.txt'
            csBag = CsBag()

            csBag.cName = ""  # 表名字
            csBag.cNameExplain = ""  # 表名字
            csBag.cAttr = []  # 属性
            csBag.cExplain = []  # 说明
            csBag.cTpey = []  # 类型
            AllCsBag.append(csBag)
            csBag.cName = txtName

            # 判断Txt文件是否存在,删除
            if os.path.exists(file_dirOut + r'\\' + target_file):
                os.remove(file_dirOut + r'\\' + target_file)

            # 打开表格
            excel_data = open_excel(file_dir + r'\\' + file)
            table = excel_data.sheets()[byindex]
            nrows = table.nrows
            ncols = table.ncols
            txtfile = open(file_dirOut + r'\\' + target_file, 'a+', encoding='utf-8')

            KeyTag = []  # 当前表的字段的名字
            for i in range(0, nrows):
                for j in range(ncols):
                    ctype = table.cell(i, j).ctype  # 类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
                    no = table.cell(i, j).value
                    if i == 0 and j == 0:
                        csBag.cNameExplain = no
                    # 判断是否是有效的列
                    if i == 1:
                        if ctype == 0 or (ctype == 1 and (len(no) == 0 or no == "#")):
                            KeyTag.append(0)
                        else:
                            KeyTag.append(1)
                            csBag.cAttr.append(firstCharUpper(no))
                    if i >= 1 and KeyTag[j] == 0:  # 去除注释
                        continue
                    if i == 2:
                        csBag.cTpey.append(no)
                    if i == 3:
                        csBag.cExplain.append(no)
                    if i > 3:
                        if ctype == 0:  # 错误都是-1
                            no = "-1"
                        if ctype == 2:  # 如果是2.000则被抹除为2
                            cno = int(no)
                            if cno == no:
                                no = cno
                        if ctype == 4:  # bool
                            if no:
                                no = r"1"
                            else:
                                no = r"0"
                            no = bool(no)
                    if j == 0:
                        cell_value = str(no)
                    if j > 0:
                        cell_value = split_str + str(no)

                    txtfile.write(cell_value)
                    j = j + 1
                i = i + 1
                txtfile.write('\n')
            txtfile.close()
            print(str(target_file) + "  Ok!")
    # print(str(TabAllName))


# --------------------------------------------------------------------------------------------------------------
if __name__ == '__main__':
    excel2txt('.xls', 0, '#')
    Set_CsName()  # 设置CS文件
    CsBagToCs()
